﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class LichGiaoDAO
    {
        public List<LichGiaoDTO> LayDSLichGiao()
        {
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            string sql = "proc_LayThongTinLichGiao ";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<LichGiaoDTO> ds = new List<LichGiaoDTO>();
            while (reader.Read())
            {
                LichGiaoDTO ctLichGiao = new LichGiaoDTO();
                ctLichGiao.MaDH = reader.GetString(0);
                ctLichGiao.NgayGioDH = reader.GetDateTime(1);
                ctLichGiao.NgayGioGH = reader.GetDateTime(2);
                ds.Add(ctLichGiao);
            }
            conn.Close();
            return ds;
        
        }
        public List<LichGiaoDTO> LayDonHangTuLichGiao(DateTime day)
        {
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            string sql = "proc_XemDHTuLG";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter par;
            par = new SqlParameter("ngaygioGH", SqlDbType.DateTime);
            par.Direction = ParameterDirection.Input;
            par.Value = day;
            cmd.Parameters.Add(par);
            SqlDataReader reader = cmd.ExecuteReader();
            List<LichGiaoDTO> ds = new List<LichGiaoDTO>();
            while (reader.Read())
            {
                LichGiaoDTO ctDTO = new LichGiaoDTO();
                ctDTO.MaDH = reader.GetString(0);
                ctDTO.NgayGioDH = reader.GetDateTime(1);
                ctDTO.NgayGioGH = reader.GetDateTime(2);
                ds.Add(ctDTO);
            }
            conn.Close();
            return ds;
        }
        public int DemDHTuLG(DateTime day)
        {
            int kq = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn = DataProvider.ConnectDB();

                string sql = "proc_DemDHTuLG";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                // cmd.Connection = conn;

                SqlParameter sqlPar;
                sqlPar = new SqlParameter("ngaygioGH", SqlDbType.DateTime);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = day;
                cmd.Parameters.Add(sqlPar);
                sqlPar = new SqlParameter("SoDH", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(sqlPar);

                kq = cmd.ExecuteNonQuery();
                kq = int.Parse(cmd.Parameters["SoDH"].Value.ToString());
                conn.Close();
            }
            catch (SqlException ex)
            {
                return kq;
            }
            return kq;
        }


        public int ThemLichGiao(LichGiaoDTO lgdto)
        {
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("proc_ThemLichGiao", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@madh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = lgdto.MaDH;
            cmd.Parameters.Add(sqlPar);

            

            sqlPar = new SqlParameter("@ngaydh", SqlDbType.DateTime);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = lgdto.NgayGioDH;
            cmd.Parameters.Add(sqlPar);
            sqlPar = new SqlParameter("@ngaygh", SqlDbType.DateTime);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = lgdto.NgayGioGH;
            cmd.Parameters.Add(sqlPar);

           

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;
        }

        public int HuyLichGiao(LichGiaoDTO lgdto)
        {
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("usp_HuyDSLG_KH", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@madh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = lgdto.MaDH;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;
        }

    }
}
